﻿Imports System.Data.SqlClient

Public Class frmInsuranceBrokersRegistry

    Sub Clear()
        Me.txtCompanyName.Clear()
        Me.txtMobileNo.Clear()
        Me.txtEmail.Clear()
        Me.txtAddress.Clear()
        Me.txtCompanyName.Focus()
    End Sub

    Sub FillInsuranceCompaniesList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From InsuranceBrokers Order By SNo", cnn)
            Dim Reader As SqlDataReader

            Me.GridInsuranceCompanies.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridInsuranceCompanies.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("CompanyName"), Reader.Item("MobileNo"), _
                                                                Reader.Item("Email"), Reader.Item("Address"), "Update", "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Clear()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            If Me.txtCompanyName.Text.Trim.Length = 0 Then
                MsgBox("Please fill in all mandatory fields")
                Exit Sub
            ElseIf Me.txtEmail.Text.Trim.Length > 0 And _
                   Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtEmail.Text.Trim, "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
                MsgBox("Please enter a valid email address or leave blank")
                Exit Sub
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Insert Into InsuranceBrokers (CompanyName,MobileNo,Email,Address) Values " & _
                                          "(@CompanyName,@MobileNo,@Email,@Address)", cnn)

                cnn.Open()
                'Add values
                cmd.Parameters.AddWithValue("@CompanyName", Me.txtCompanyName.Text.Trim)
                cmd.Parameters.AddWithValue("@MobileNo", Me.txtMobileNo.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text.Trim)

                cmd.ExecuteNonQuery()
                cnn.Close()

                MsgBox("Saved Successfully!")

                Clear()

                FillInsuranceCompaniesList()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridInsuranceCompanies_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridInsuranceCompanies.CellClick
        If e.ColumnIndex = Me.DGColUpdate.Index Then
            Dim a As New frmInsuranceBrokersRegistryUpdate
            a.SNo = Me.GridInsuranceCompanies.CurrentRow.Cells(0).Value
            a.ShowDialog()

            FillInsuranceCompaniesList()

        ElseIf e.ColumnIndex = Me.DGColDelete.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From InsuranceBrokers Where SNo=" & Me.GridInsuranceCompanies.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillInsuranceCompaniesList()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub

    Private Sub frmInsuranceBrokersRegistry_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        FillInsuranceCompaniesList()
    End Sub
End Class